﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using ERP_RORZE_SYSTEM;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F4TimeReport : UserControl
    {
        private INISettings mySettings;
        private SqlConnection myConnection;

        public panel_F4TimeReport()
        {
            InitializeComponent();
            this.toDate.DateTime = DateTime.Now;
            this.mySettings = new INISettings();
            this.myConnection = new SqlConnection(mySettings.SQLConnectionString);
            getStaffList();
            getOrderList();

            this.fromOrder.LostFocus += fromOrder_LostFocus;
            this.toOrder.LostFocus += toOrder_LostFocus;
        }

        void toOrder_LostFocus(object sender, EventArgs e)
        {
            for (int i = 0; i < this.toOrder.Properties.Items.Count; i++)
            {
                ComboboxItem item = (ComboboxItem)this.toOrder.Properties.Items[i];
                if (item.Value.ToString().ToUpper() == this.toOrder.Text.Trim().ToUpper())
                {
                    this.toOrder.SelectedIndex = i;
                }
            }
        }

        void fromOrder_LostFocus(object sender, EventArgs e)
        {
            for (int i = 0; i < this.fromOrder.Properties.Items.Count; i++)
            {
                ComboboxItem item = (ComboboxItem)this.fromOrder.Properties.Items[i];
                if (item.Value.ToString().ToUpper() == this.fromOrder.Text.Trim().ToUpper())
                {
                    this.fromOrder.SelectedIndex = i;
                }
            }
            this.toOrder.SelectedIndex = this.fromOrder.SelectedIndex;
        }

        private void getStaffList()
        {
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT [StaffID] FROM [222_Staff]"
                    + " WHERE [DepartmentID] IN ('231', '232')";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["StaffID"].ToString().Trim();
                    item.Value = dataReader["StaffID"].ToString().Trim();
                    this.staffID.Properties.Items.Add(item);
                }
                dataReader.Close();
                sql_cmd.Dispose();
            }
            catch
            {
                MessageBox.Show("Cannot get Staff list");
            }
            finally
            {
                myConnection.Close();
            }
        }

        private void getOrderList()
        {
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT [PlanNo] FROM [231_TimePlan]"
                    + " WHERE (DeadLine > DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) - 365) GROUP BY [PlanNo] ORDER BY [PlanNo]";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["PlanNo"].ToString().Trim();
                    item.Value = dataReader["PlanNo"].ToString().Trim();
                    this.fromOrder.Properties.Items.Add(item);
                    this.toOrder.Properties.Items.Add(item);
                }
                dataReader.Close();
                sql_cmd.Dispose();

                sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT [TimePlanNo], [SubOrder6], [SubOrder1] FROM [231_TimePlan]"
                    + " WHERE (DeadLine > DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) - 365)"
                    + " GROUP BY [TimePlanNo], [SubOrder6], [SubOrder1]"
                    + " ORDER BY [TimePlanNo], [SubOrder6], [SubOrder1]";
                dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    if (!string.IsNullOrEmpty(dataReader["TimePlanNo"].ToString().Trim()))
                    {
                        ComboboxItem item = new ComboboxItem();
                        item.Text = dataReader["TimePlanNo"].ToString().Trim();
                        item.Value = dataReader["TimePlanNo"].ToString().Trim();
                        this.fromOrder.Properties.Items.Add(item);
                        this.toOrder.Properties.Items.Add(item);
                    }

                    if (!string.IsNullOrEmpty(dataReader["SubOrder6"].ToString().Trim()))
                    {
                        ComboboxItem item = new ComboboxItem();
                        item.Text = dataReader["SubOrder6"].ToString().Trim();
                        item.Value = dataReader["SubOrder6"].ToString().Trim();
                        this.fromOrder.Properties.Items.Add(item);
                        this.toOrder.Properties.Items.Add(item);
                    }

                    if (!string.IsNullOrEmpty(dataReader["SubOrder1"].ToString().Trim()))
                    {
                        ComboboxItem item = new ComboboxItem();
                        item.Text = dataReader["SubOrder1"].ToString().Trim();
                        item.Value = dataReader["SubOrder1"].ToString().Trim();
                        this.fromOrder.Properties.Items.Add(item);
                        this.toOrder.Properties.Items.Add(item);
                    }
                }
                dataReader.Close();
                sql_cmd.Dispose();
            }
            catch
            {
                MessageBox.Show("Cannot get OrderNo and PlanNo list");
            }
            finally
            {
                myConnection.Close();
            }
        }

        private void buttonTimeReport_Click(object sender, EventArgs e)
        {
            F4TimeReport.Columns.Clear();
            gridF4TimeReport.DataSource = null;
            
            if ((string.IsNullOrEmpty(this.fromDate.EditValue.ToString())) || (string.IsNullOrEmpty(this.toDate.EditValue.ToString())))
            {
                MessageBox.Show("Please select range date to view report.");
            }
            else
            {
                if (this.staffID.SelectedIndex < 0)
                {
                    MessageBox.Show("Please select Staff ID.");
                }
                else
                {
                    ComboboxItem staff = (ComboboxItem)this.staffID.SelectedItem;

                    DateTime fromdate = this.fromDate.DateTime;
                    DateTime todate = this.toDate.DateTime;
                    DataSet sourceDataSet = new DataSet();
                    string sql = "SELECT t2.[Date], t2.StaffID, t1.StaffName, t2.CompType, t2.TimePlanNo, t2.SubOrder,"
                        + " t2.TimeWorkID, t3.TimeWorkName, SUM(t2.AssemblyTime) AS TotalTime, t2.[Note]"
                        + " FROM [222_Staff] AS t1 INNER JOIN"
                        + " [231_TimeInput] AS t2 INNER JOIN"
                        + " [231_TimeWork] AS t3 ON t2.TimeWorkID = t3.TimeWorkID ON t1.StaffID = t2.StaffID"
                        + " WHERE (t2.[Date] BETWEEN @fromDate AND @toDate) AND (t2.F5 = 0)"
                        + " AND (t1.StaffID = '" + staff.Value.ToString() + "')"
                        + " GROUP BY t2.[Date], t2.StaffID, t2.CompType, t2.TimePlanNo, t2.SubOrder, t2.TimeWorkID,"
                        + " t3.TimeWorkName, t1.StaffName, t2.[Note]";
                    try
                    {
                        //sourceDataSet = null;
                        myConnection.Open();
                        SqlCommand cmd = new SqlCommand(sql, myConnection);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        cmd.Parameters.Add("@fromDate", SqlDbType.DateTime).Value = fromdate;
                        cmd.Parameters.Add("@toDate", SqlDbType.DateTime).Value = todate;
                        adapter.Fill(sourceDataSet);
                        adapter.Dispose();
                        cmd.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        sourceDataSet = null;
                    }

                    if (sourceDataSet != null)
                    {
                        gridF4TimeReport.DataSource = sourceDataSet.Tables[0];
                    }
                    else
                    {
                        DataTable newTable = new DataTable();
                        newTable.Columns.Add("Date", typeof(DateTime));
                        newTable.Columns.Add("StaffID", typeof(string));
                        newTable.Columns.Add("StaffName", typeof(string));
                        newTable.Columns.Add("CompType", typeof(string));
                        newTable.Columns.Add("TimePlanNo", typeof(string));
                        newTable.Columns.Add("SubOrder", typeof(string));
                        newTable.Columns.Add("TimeWorkID", typeof(string));
                        newTable.Columns.Add("TimeWorkName", typeof(string));
                        newTable.Columns.Add("TotalTime", typeof(float));
                        newTable.Columns.Add("Note", typeof(string));
                        gridF4TimeReport.DataSource = newTable;
                    }
                    F4TimeReport.Columns[0].Caption = "Date";
                    F4TimeReport.Columns[1].Caption = "Staff ID";
                    F4TimeReport.Columns[2].Caption = "Staff Name";
                    F4TimeReport.Columns[3].Caption = "Comp Type";
                    F4TimeReport.Columns[4].Caption = "TimePlan No";
                    F4TimeReport.Columns[5].Caption = "SubOrder";
                    F4TimeReport.Columns[6].Caption = "TimeWork ID";
                    F4TimeReport.Columns[7].Caption = "TimeWork Name";
                    F4TimeReport.Columns[8].Caption = "Total Time";
                    F4TimeReport.Columns[9].Caption = "Note";
                    F4TimeReport.BestFitColumns();
                    F4TimeReport.ViewCaption = "Staff Time Report";
                }
            }
        }

        private void buttonOrderTimeReport_Click(object sender, EventArgs e)
        {
            F4TimeReport.Columns.Clear();
            gridF4TimeReport.DataSource = null;

            if ((string.IsNullOrEmpty(this.fromDate.EditValue.ToString())) || (string.IsNullOrEmpty(this.toDate.EditValue.ToString())))
            {
                MessageBox.Show("Please select range date to view report.");
            }
            else
            {
                bool pass = true;
                string fromorder = null;
                string toorder = null;
                ComboboxItem item = new ComboboxItem();
                if (this.fromOrder.SelectedIndex < 0)
                {
                    MessageBox.Show("Please select From Order.");
                    pass = false;
                }
                if (pass)
                {
                    if (this.toOrder.SelectedIndex < 0)
                    {
                        MessageBox.Show("Please select To Order.");
                        pass = false;
                    }
                }
                if (pass)
                {
                    item = (ComboboxItem)this.fromOrder.SelectedItem;
                    fromorder = item.Value.ToString();
                    item = (ComboboxItem)this.toOrder.SelectedItem;
                    toorder = item.Value.ToString();
                    if (checkOrder(fromorder) != checkOrder(toorder))
                    {
                        MessageBox.Show("From OrderNo/PlanNo and To OrderNo/PlanNo is not matched");
                        pass = false;
                    }
                }
                if (pass)
                {
                    DateTime fromdate = this.fromDate.DateTime;
                    DateTime todate = this.toDate.DateTime;
                    DataSet sourceDataSet = new DataSet();
                    string sql = null;
                    if (checkOrder(fromorder) == 0)
                    {
                        sql = "SELECT t1.Date, t1.StaffID, t3.StaffName, t1.TimeWorkID, t2.TimeWorkName, t1.TimePlanNo,"
                            + " SUM(t1.[AssemblyTime]) AS [TotalTime], t1.[SubOrder], t1.[Note], t3.[GroupID], t1.CompType"
                            + " FROM [231_TimeInput] AS t1 INNER JOIN"
                            + " [231_TimeWork] AS t2 ON t1.TimeWorkID = t2.TimeWorkID INNER JOIN"
                            + " [222_Staff] AS t3 ON t1.StaffID = t3.StaffID"
                            + " WHERE t1.[F5] = 0"
                            + " GROUP BY t1.Date, t1.StaffID, t3.StaffName, t1.TimeWorkID, t2.TimeWorkName, t1.TimePlanNo,"
                            + " t1.[SubOrder], t1.[Note], t3.[GroupID], t1.CompType, t1.[F5]"
                            + " HAVING ((t1.TimePlanNo BETWEEN @fromOrder AND @toOrder)"
                            + " AND (t1.Date BETWEEN @fromDate AND @toDate)) OR ((t1.Date BETWEEN @fromDate AND @toDate)"
                            + " AND (t1.[SubOrder] BETWEEN @fromOrder AND @toOrder))"
                            + " ORDER BY t1.TimePlanNo, t1.[SubOrder]";
                    }
                    if (checkOrder(fromorder) == 1)
                    {
                        sql = "SELECT t1.Date, t4.PlanNo, t1.StaffID, t3.StaffName, t1.TimeWorkID, t2.TimeWorkName,"
                            + " t1.TimePlanNo, SUM(t1.[AssemblyTime]) AS [TotalTime], t1.[SubOrder], t1.[Note],"
                            + " t3.[GroupID], t1.CompType"
                            + " FROM [231_TimeInput] AS t1 INNER JOIN"
                            + " [231_TimeWork] AS t2 ON t1.TimeWorkID = t2.TimeWorkID INNER JOIN"
                            + " [222_Staff] AS t3 ON t1.StaffID = t3.StaffID INNER JOIN"
                            + " [231_TimePlan] AS t4 ON t1.TimePlanNo = t4.TimePlanNo"
                            + " WHERE t1.[F5] = 0"
                            + " GROUP BY t1.Date, t1.StaffID, t3.StaffName, t1.TimeWorkID, t2.TimeWorkName,"
                            + " t1.TimePlanNo, t1.[SubOrder], t1.[Note], t3.[GroupID], t1.CompType, t4.PlanNo"
                            + " HAVING (t1.Date BETWEEN @fromDate AND @toDate)"
                            + " AND (t4.PlanNo BETWEEN @fromOrder AND @toOrder)"
                            + " ORDER BY t4.PlanNo, t1.TimePlanNo";
                    }
                    try
                    {
                        //sourceDataSet = null;
                        myConnection.Open();
                        SqlCommand cmd = new SqlCommand(sql, myConnection);
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        cmd.Parameters.Add("@fromDate", SqlDbType.DateTime).Value = fromdate;
                        cmd.Parameters.Add("@toDate", SqlDbType.DateTime).Value = todate;
                        cmd.Parameters.Add("@fromOrder", SqlDbType.NVarChar).Value = fromorder;
                        cmd.Parameters.Add("@toOrder", SqlDbType.NVarChar).Value = toorder;
                        adapter.Fill(sourceDataSet);
                        adapter.Dispose();
                        cmd.Dispose();
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                        sourceDataSet = null;
                    }

                    if (sourceDataSet != null)
                    {
                        gridF4TimeReport.DataSource = sourceDataSet.Tables[0];
                    }
                    else
                    {
                        DataTable newTable = new DataTable();
                        newTable.Columns.Add("Date", typeof(DateTime));
                        newTable.Columns.Add("StaffID", typeof(string));
                        newTable.Columns.Add("StaffName", typeof(string));
                        newTable.Columns.Add("TimeWorkID", typeof(string));
                        newTable.Columns.Add("TimeWorkName", typeof(string));
                        newTable.Columns.Add("TimePlanNo", typeof(string));
                        newTable.Columns.Add("TotalTime", typeof(float));
                        newTable.Columns.Add("OrderNo", typeof(string));
                        newTable.Columns.Add("Note", typeof(float));
                        newTable.Columns.Add("GroupID", typeof(string));
                        newTable.Columns.Add("CompType", typeof(string));                        
                        gridF4TimeReport.DataSource = newTable;
                    }
                    if (checkOrder(fromorder) == 0)
                    {
                        F4TimeReport.Columns[0].Caption = "Date";
                        F4TimeReport.Columns[1].Caption = "Staff ID";
                        F4TimeReport.Columns[2].Caption = "Staff Name";
                        F4TimeReport.Columns[3].Caption = "TimeWork ID";
                        F4TimeReport.Columns[4].Caption = "TimeWork Name";
                        F4TimeReport.Columns[5].Caption = "TimePlan No";
                        F4TimeReport.Columns[6].Caption = "Total Time";
                        F4TimeReport.Columns[7].Caption = "Order No";
                        F4TimeReport.Columns[8].Caption = "Note";
                        F4TimeReport.Columns[9].Caption = "Staff Group";
                        F4TimeReport.Columns[10].Caption = "Comp Type";                        
                    }
                    if (checkOrder(fromorder) == 1)
                    {
                        F4TimeReport.Columns[0].Caption = "Date";
                        F4TimeReport.Columns[1].Caption = "Plan No";
                        F4TimeReport.Columns[2].Caption = "Staff ID";
                        F4TimeReport.Columns[3].Caption = "Staff Name";
                        F4TimeReport.Columns[4].Caption = "TimeWork ID";
                        F4TimeReport.Columns[5].Caption = "TimeWork Name";
                        F4TimeReport.Columns[6].Caption = "TimePlan No";
                        F4TimeReport.Columns[7].Caption = "Total Time";
                        F4TimeReport.Columns[8].Caption = "Order No";
                        F4TimeReport.Columns[9].Caption = "Note";
                        F4TimeReport.Columns[10].Caption = "Staff Group";
                        F4TimeReport.Columns[11].Caption = "Comp Type";
                    }
                    F4TimeReport.BestFitColumns();
                    F4TimeReport.ViewCaption = "Order Time Report";
                }
            }
        }               

        private void buttonFinishedDate_Click(object sender, EventArgs e)
        {
            F4TimeReport.Columns.Clear();
            gridF4TimeReport.DataSource = null;

            if ((string.IsNullOrEmpty(this.fromDate.EditValue.ToString())) || (string.IsNullOrEmpty(this.toDate.EditValue.ToString())))
            {
                MessageBox.Show("Please select range date to view report.");
            }
            else
            {
                DateTime fromdate = this.fromDate.DateTime;
                DateTime todate = this.toDate.DateTime;
                DataSet sourceDataSet = new DataSet();
                string sql = "SELECT t1.[Date], t4.[PlanNo], t4.[CompID], t4.[Quantity], t4.[FinishedDate], t1.StaffID,"
                    + " t3.StaffName, t1.TimeWorkID, t2.TimeWorkName, t1.TimePlanNo, SUM(t1.AssemblyTime) AS [TotalTime],"
                    + " t1.[SubOrder], t1.[Note], t3.[GroupID], t1.[CompType]"
                    + " FROM [231_TimeInput] AS t1 INNER JOIN"
                    + " [231_TimeWork] AS t2 ON t1.TimeWorkID = t2.TimeWorkID INNER JOIN"
                    + " [222_Staff] AS t3 ON t1.StaffID = t3.StaffID INNER JOIN"
                    + " [231_TimePlan] AS t4 ON t1.TimePlanNo = t4.TimePlanNo"
                    + " GROUP BY t1.Date, t1.StaffID, t3.StaffName,"
                    + " t1.TimeWorkID, t2.TimeWorkName, t1.TimePlanNo, t1.[SubOrder],"
                    + " t1.[Note], t3.[GroupID], t1.[CompType], t1.F5,"
                    + " t4.[PlanNo], t4.[CompID], t4.Quantity, t4.[FinishedDate]"
                    + " HAVING (t4.[FinishedDate] BETWEEN @fromDate AND @toDate) AND (t1.F5 = 0)"
                    + " ORDER BY t4.[FinishedDate], t4.PlanNo";
                try
                {                    
                    myConnection.Open();
                    SqlCommand cmd = new SqlCommand(sql, myConnection);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    cmd.Parameters.Add("@fromDate", SqlDbType.DateTime).Value = fromdate;
                    cmd.Parameters.Add("@toDate", SqlDbType.DateTime).Value = todate;
                    cmd.CommandTimeout = 30000;
                    adapter.Fill(sourceDataSet);
                    adapter.Dispose();
                    cmd.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    sourceDataSet = null;
                }

                if (sourceDataSet != null)
                {
                    gridF4TimeReport.DataSource = sourceDataSet.Tables[0];
                }
                else
                {
                    DataTable newTable = new DataTable();
                    newTable.Columns.Add("Date", typeof(DateTime));
                    newTable.Columns.Add("PlanNo", typeof(string));
                    newTable.Columns.Add("CompID", typeof(string));
                    newTable.Columns.Add("Quantity", typeof(float));
                    newTable.Columns.Add("FinishedDate", typeof(DateTime));
                    newTable.Columns.Add("StaffID", typeof(string));
                    newTable.Columns.Add("StaffName", typeof(string));
                    newTable.Columns.Add("TimeWorkID", typeof(string));
                    newTable.Columns.Add("TimeWorkName", typeof(string));
                    newTable.Columns.Add("TimePlanNo", typeof(string));
                    newTable.Columns.Add("TotalTime", typeof(float));
                    newTable.Columns.Add("SubOrder", typeof(string));
                    newTable.Columns.Add("Note", typeof(string));
                    newTable.Columns.Add("GroupID", typeof(string));                    
                    newTable.Columns.Add("CompType", typeof(string));                    
                    gridF4TimeReport.DataSource = newTable;
                }
                F4TimeReport.Columns[0].Caption = "Date";
                F4TimeReport.Columns[1].Caption = "Plan No";
                F4TimeReport.Columns[2].Caption = "Comp ID";
                F4TimeReport.Columns[3].Caption = "Quantity";
                F4TimeReport.Columns[4].Caption = "Finished Date";
                F4TimeReport.Columns[5].Caption = "Staff ID";
                F4TimeReport.Columns[6].Caption = "Staff Name";
                F4TimeReport.Columns[7].Caption = "TimeWork ID";
                F4TimeReport.Columns[8].Caption = "TimeWork Name";
                F4TimeReport.Columns[9].Caption = "TimePlan No";
                F4TimeReport.Columns[10].Caption = "Total Time";
                F4TimeReport.Columns[11].Caption = "SubOrder";
                F4TimeReport.Columns[12].Caption = "Note";
                F4TimeReport.Columns[13].Caption = "Group";
                F4TimeReport.Columns[14].Caption = "Comp Type";
                F4TimeReport.BestFitColumns();
                F4TimeReport.ViewCaption = "Report By Finished Date";
            }
        }

        private int checkOrder(string order)
        {
            int ret = 0;
            int check;
            if (Int32.TryParse(order.ToUpper().Substring(1, 6), out check))
            {
                ret = 0;
            }
            if ((Int32.TryParse(order.ToUpper().Substring(2, 5), out check)) && (order.ToUpper().Substring(1, 1) == "A"))
            {
                ret = 1;
            }
            return ret;
        }
    }
}
